/*NOTE: This is model code to illustrate the logic used to generate the datasets for analysis in the SCARP pilot.
This code is not intended to run as is - due to Epic restrictions on the sharing of intellectual property all 
of the table and attribute names have been "masked" with names enclosed in square brackets if they were pulled 
from Clarity (EPIC).*/

/*****************************************************
PROJECT:  BRIDGE C2
PILOT: SCRAP
DATE: 9/6/2022

DESCRIPTION: Pull mammography dates for BC cohort



*****************************************************/;
options compress=yes reuse=yes;
libname RD odbc noprompt="dsn=research_dev;Trusted_Connection=yes" bulkload=yes;
libname SCRAP "e:\\sasroot\BRIDGE C2\SCRAP\";


proc sql;
create table rd.jpo_bc_cohort as select* from scrap.scrap_BC_patients;
quit;

/*internal code to create scrap.BC_mammography_codes*/

proc sql; create table RD.jpo_mammography_lu
as select * from scrap.BC_mammography_codes; 
quit;


/****************************************************/
/*pull from surgical history*/
proc sql;
connect to odbc as mycon
(required = "dsn=research_dev;Trusted_Connection=yes");
create table BC_shx as 
select * from connection to mycon (
     SELECT den.raw_patid
	,cpc.[procedure identifier]
	,cpc.[procedure name]	
  	,shx.[contact date] 
	,shx.[surgical history start date]
     ,shx.[SURGICAL HISTORY DATE] 
    FROM research_dev.dbo.jpo_BC_cohort  AS den
     INNER JOIN clarity.dbo.[SURGICAL HISTORY]        AS shx
      ON den.raw_patID = shx.[patient identifier]
     INNER JOIN research_dev.dbo.jpo_mammography_lu AS cpc
      ON shx.[procedure identifier] = cpc.[procedure identifier])
	;      
quit;


/*screens are accepted within 27 months of report_date*/
data BC_SHX2 ;
	set BC_SHX;
	length source $20;
	Source='Surgical Hx';
	month=0;
	day=0;
	format screen_date date9.;
	if [surgical history start date]^=. then
		do;
			screen_date=datepart([surgical history start date]);
			if screen_date>=intnx('month','01JUL2016'd,-27,'same') and screen_date<='29FEB2020'd then 
				output BC_SHX2;
		end;
	if [surgical history start date]=. then
		do;
			if [SURGICAL HISTORY DATE]^=' ' and [SURGICAL HISTORY DATE]^='NO REPORT' then
				do;
					if index([SURGICAL HISTORY DATE],'/')^=0 then
						do;
							month=input (scan([SURGICAL HISTORY DATE],1,'/'),best4.);
							day=input (scan([SURGICAL HISTORY DATE],2,'/'),best4.);
							year=input (scan([SURGICAL HISTORY DATE],3,'/'),best4.);
						end;
					if index([SURGICAL HISTORY DATE],'-')^=0 then
						do;
							month=input (scan([SURGICAL HISTORY DATE],1,'-'),best4.);
							day=input (scan([SURGICAL HISTORY DATE],2,'-'),best4.);
							year=input (scan([SURGICAL HISTORY DATE],3,'-'),best4.);
						end;
					if month>0 and month<13 and
						day>0 and day<32 and
						year>2005 and year<2022 then
							do;
								screen_date=mdy(month, day, year);
							end;
			if screen_date>=intnx('month','01JUL2016'd,-27,'same') and screen_date<='29FEB2020'd then 
				output BC_SHX2;
				end;
		end;
run;


*screening orders and results from order_proc;
proc sql;
connect to odbc as mycon
(required = "dsn=research_dev;Trusted_Connection=yes");
create table BC_orders as 
select * from connection to mycon (

SELECT distinct
	den.raw_patid
    ,op.[ORDERING DATE]
	,op.[result time]
	,op.[procedure identifier]
	,ppc.[procedure name]
	,order_status_c
	,op.[pending order yes/no]
	,op.[order procedure identifier]
	,op.[abnormal yes/no]
	,op.[display name]
	,op.[ORDERING DATE]
	,op.[result time] 	
 FROM 
	research_dev.dbo.jpo_BC_cohort  AS den
		inner join Clarity.dbo.[ORDER PROCEDURE] as op
			on den.raw_patid = op.[patient identifier] 
        inner join research_dev.dbo.jpo_mammography_lu  AS ppc
      ON op.[procedure identifier] = ppc.[procedure identifier]);
quit;
*3=resulted 5=completed;
*2=sent ;
* 4 canceled ;

data BC_orders2 ;
set BC_orders;
length source $20;
Source='Order proc';
format order_date date9.;
order_date=datepart([ORDERING DATE]);
where order_status_c^=4;
if order_date<intnx('month','01JUL2016'd,-27,'same') then delete;
if order_date>'29FEB2020'd then delete;
run;

data BC_screen_procs;
set BC_orders;
format screen_date date9.;
screen_date=datepart([result time]);
where [result time]^=.;
if screen_date<intnx('month','01JUL2016'd,-27,'same') then delete;
if screen_date>'29FEB2020'd then delete;
run;

/*pull screens from advance lab results using loinc code;*/
proc sql;
create table lab_results1 as
     select distinct d.raw_patid, 
	r.lab_loinc,
	r.sas_lab_order_date,
	r.sas_result_date
     from ochin.lab_result_cm r
	inner join scrap.scrap_bc_patients d
	on d.raw_patid=r.raw_patid
     inner join reschdev.jpo_mammography_lu m
	 on r.lab_loinc=strip(m.lnc_code);
quit;

proc sql;
create table lab_results2 as
     select distinct d.raw_patid, 
	r.lab_loinc,
	r.sas_lab_order_date,
	r.sas_result_date
     from ochin.lab_result_cm r
	inner join scrap.scrap_bc_patients d
	on d.raw_patid=r.raw_patid
     inner join reschdev.jpo_mammography_lu m
 	on m.[procedure identifier]=input(r.raw_lab_code, best10.);   
quit;

data lab_results;
set lab_results lab_results2;
run;


data ADV_lab_orders (drop=sas_order_date sas_result_date);
set lab_results;
format screen_date date9.;
order_date=sas_result_date;
if order_date<intnx('month','01JUL2016'd,-27,'same') then delete;
if order_date>'29FEB2020'd then delete;
run;
data ADV_lab_results (drop=sas_order_date sas_result_date);
set lab_results;
format screen_date date9.;
screen_date=sas_result_date;
if screen_date<intnx('month','01JUL2016'd,-27,'same') then delete;
if screen_date>'29FEB2020'd then delete;
run;


/*Screens from HM_history*/
proc sql;
connect to odbc as mycon
(required = "dsn=OCHIN_HCN_FENWAY;Trusted_Connection=yes");
create table HMT_hx as select *
from connection to mycon (
SELECT 
	p.raw_patid
    ,hmt.[HEALTH MAINTENANCE TOPIC NAME]
	,hmo.[HEALTH MAINTENANCE TOPIC IDENTIFIER]
    ,hmo.[HEALTH MAINTENANCE HISTORY Date]
FROM 
	Clarity.dbo.[HEALTH MAINTENANCE HISTORY] hmo
		INNER JOIN research_dev.dbo.jpo_BC_cohort p
			ON hmo.[patient identifier] = p.raw_PATID
		LEFT JOIN Clarity.dbo.[HEALTH MAINTENANCE TOPIC] hmt
			ON hmt.[HEALTH MAINTENANCE TOPIC IDENTIFIER] = hmo.[HEALTH MAINTENANCE TOPIC IDENTIFIER]
WHERE 
	
		(hmo.[HEALTH MAINTENANCE TOPIC IDENTIFIER] =6) 
	and hmo.[HEALTH MAINTENANCE TYPE] = 1 );

data HMT_HX;
set HMT_HX;
length source $20;
format screen_date date9.;
screen_date=datepart([HEALTH MAINTENANCE HISTORY Date]);
Source='HMT HX';
if screen_date<intnx('month','01JUL2016'd,-27,'same') then delete;
if screen_date>'29FEB2020'd then delete;
run;


/*combine orders*/
data orders;
set ADV_lab_orders BC_orders2;
run;

*deduplicate;
proc sql;
create table orders2  as
select raw_patid,
order_date,
max([order procedure identifier]^=.) as in_Clarity,
max([order procedure identifier]=.) as in_ADV_results
from orders
group by raw_patid,
order_date;

data scrap.BC_mammogram_order_dates;
set orders2;
run;

/* combine screens */
data screens;
set BC_screen_procs ADV_Lab_results BC_shx2 HMT_HX;
run;


/*deduplicate*/
proc sql;
create table BC_screen_dates as
select raw_patid,
screen_date,
sum(source='HMT HX') as HMT_HX_records,
sum(source in('Lab CC LNC','Lab Component')) as Lab_re_records,
sum(source='Order proc') as ord_proc_records,
sum(source='Surgical Hx') as Surg_hx_records,
sum(source=' ') as ADV_lab_result_records
from screens
group by raw_patid,
screen_date;


/*add next due*/

data SCRAP.BC_screen_dates;
set BC_screen_dates;
format next_due date9.;
next_due=intnx('month',screen_date,27,'same');
run;


